package net.dreamlu.rapid.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import net.dreamlu.rapid.model.DBColumn;
import net.dreamlu.rapid.model.DBTable;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;


/**
 * 数据库帮助类，用来获取数据库
 * @author L.cm
 * email: 596392912@qq.com
 * site:http://www.dreamlu.net
 * @date 2014年9月10日 上午10:28:02
 */
public class DBUtils {

	private static DataSource ds = null; 

	/**
	 * 注入dataSource
	 * @return
	 */
	public static void initDataSource(Properties props) {
		MysqlDataSource mysql = new MysqlDataSource();
		mysql.setUrl(props.getProperty("db.jdbcUrl"));
		mysql.setUser(props.getProperty("db.user"));
		mysql.setPassword(props.getProperty("db.password"));
		ds = mysql;
	}

	/**
	 * 组装所有的表
	 * @return
	 * @throws SQLException
	 */
	public static List<DBTable> tables() throws SQLException {
		Connection conn = ds.getConnection();
		DatabaseMetaData metaData = conn.getMetaData();
		// 组装所有的表
		ResultSet rs = metaData.getTables(conn.getCatalog(), "root", null, new String[]{"TABLE"});
		List<DBTable> tables = new ArrayList<DBTable>();
		while(rs.next()) {
			String name    = rs.getString("TABLE_NAME");
			String remarks = rs.getString("REMARKS");
			DBTable table  = new DBTable(name, remarks);
			tables.add(table);
		}
		rs.close();
		// 组装所有的表结构
		for (DBTable table : tables) {
			// 获取主键
			ResultSet rss = metaData.getPrimaryKeys(conn.getCatalog(), conn.getSchema(), table.name);
			String primaryKey = null;
			while (rss.next()) {
				primaryKey = rss.getString("COLUMN_NAME");
			}
			rss.close();
			table.setPrimaryKey(primaryKey);

			// 获取结构
			ResultSet columnRs = metaData.getColumns(conn.getCatalog(), conn.getSchema(), table.name, null);
			while (columnRs.next()) {
				// 列名
				String name    = columnRs.getString("COLUMN_NAME");
				// 备注
				String remarks = columnRs.getString("REMARKS");
				// 字段类别
				String type    = columnRs.getString("TYPE_NAME");
				// 字段长度
				int size       = columnRs.getInt("COLUMN_SIZE");

				/*
					TABLE_CAT=0,			oauth
					TABLE_SCHEM=1,			null
					TABLE_NAME=2,			oauth_access_tokens
					COLUMN_NAME=3,			access_token
					DATA_TYPE=4,			12
					TYPE_NAME=5,			VARCHAR
					COLUMN_SIZE=6,			40
					BUFFER_LENGTH=7,		65535
					DECIMAL_DIGITS=8,		null
					NUM_PREC_RADIX=9,		10
					NULLABLE=10,			0
					REMARKS=11,				access_token
					COLUMN_DEF=12,			null
					SQL_DATA_TYPE=13,		0
					SQL_DATETIME_SUB=14,	0
					CHAR_OCTET_LENGTH=15,	40
					ORDINAL_POSITION=16,	1
					IS_NULLABLE=17,			NO
					SCOPE_CATALOG=18,		null
					SCOPE_SCHEMA=19,		null
					SCOPE_TABLE=20,			null
					SOURCE_DATA_TYPE=21,	null
					IS_AUTOINCREMENT=22,	NO
					IS_GENERATEDCOLUMN=23
				*/
				boolean isPk = (null != primaryKey && primaryKey.equals(name));
				DBColumn column = new DBColumn(name, remarks, isPk, type, size);
				table.addColumn(column);
			}
			columnRs.close();
		}
		return tables;
	}
}
